drop sequence PRODUCTX_SEQ;
drop SEQUENCE CATEGORY_SEQ;
drop SEQUENCE PRICECHANGE_SEQ;

ALTER TABLE PRODUCTX DROP PRIMARY KEY CASCADE;
DROP TABLE PRODUCTX CASCADE CONSTRAINTS;

ALTER TABLE CATEGORY DROP PRIMARY KEY CASCADE;
DROP TABLE CATEGORY CASCADE CONSTRAINTS;

ALTER TABLE PRICECHANGE DROP PRIMARY KEY CASCADE;
DROP TABLE PRICECHANGE CASCADE CONSTRAINTS;

CREATE SEQUENCE CATEGORY_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE NOCACHE NOORDER;


CREATE TABLE CATEGORY
(
  id NUMBER(13,0),
  name VARCHAR2(40) NOT NULL,
  CONSTRAINT PK_category PRIMARY KEY (id )
);


CREATE SEQUENCE PRODUCTX_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE NOCACHE NOORDER;

CREATE TABLE productX
(
  id NUMBER(13,0),
  price NUMBER(12,2) NOT NULL,
  description VARCHAR2(40) NOT NULL,
  categoryid NUMBER(13,0),
  CONSTRAINT PK_productX PRIMARY KEY (id ),
  constraint fk_productx_1 foreign key (categoryid)
	references CATEGORY (id)
);


CREATE SEQUENCE PRICECHANGE_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE NOCACHE NOORDER;


CREATE TABLE PRICECHANGE
(
  id NUMBER(13,0),
  notes VARCHAR2(40) NULL,
  oldprice NUMBER(12,2) NOT NULL,
  increase NUMBER(12,2) NOT NULL,
  productid NUMBER(13,0),
  CONSTRAINT PK_PRICECHANGE PRIMARY KEY (id ),
  constraint fk_pricechange_1 foreign key (productid)
	references productx (id)
);







--Insertions
insert into category(id, name) values(category_seq.NEXTVAL, 'Furniture');
insert into category(id, name) values(category_seq.NEXTVAL, 'Toys');
insert into category(id, name) values(category_seq.NEXTVAL, 'Medical');

insert into productX(id, price, description, categoryid) values(productX_seq.NEXTVAL, 12.75, 'Lamp', 1);
insert into productX(id, price, description, categoryid) values(productX_seq.NEXTVAL, 75.25, 'Table', 1);
insert into productX(id, price, description, categoryid) values(productX_seq.NEXTVAL, 0.79, 'Balloon',2);
insert into productX(id, price, description, categoryid) values(productX_seq.NEXTVAL, 5.45, 'Aspirin',3);

commit;


